/**
 * $version:  0.1 
 * $Date: 2011-01-21 
 *
 * Copyright (C) 2010-2011 Jawa Software. All rights reserved.
 *
 */

package org.jawa.user;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.List;

import org.apache.commons.codec.digest.DigestUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.jawa.database.DbConnectionManager;
import org.jawa.util.JawaConstants;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 用户操作的默认数据库实现.
 * 
 * @author Matt Tucker
 */
public class DefaultUserProvider implements UserProvider {

	private static final Logger Log = LoggerFactory.getLogger(DefaultUserProvider.class);

	private static final String LOAD_USER_BY_USERNAME = "SELECT *  FROM app_user WHERE login_no=?";
	private static final String LOAD_USER_BY_ID = "SELECT * FROM app_user WHERE user_id=? ";
	private static final String USER_COUNT = "SELECT count(*) FROM app_user";
	private static final String ALL_USERS = "SELECT * FROM app_user ORDER BY br_id  ";
	private static final String INSERT_USER = "INSERT INTO app_user (user_id,login_no,br_id,user_type,user_name,user_sex,user_status,user_password,user_picture,user_nation,user_place,user_polity,user_email,user_phone,user_reg_time,mody_pass_time,last_login_time,remark) "
			+ " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	private static final String DELETE_USER = "DELETE FROM app_user WHERE user_id=? ";

	/**
	 * @see UserProvider#loadUserByUserid(Long)
	 */
	public User loadUserByUserid(Long userId) throws UserNotFoundException {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(LOAD_USER_BY_ID);
			pstmt.setLong(1, userId);
			rs = pstmt.executeQuery();
			if (!rs.next()) {
				throw new UserNotFoundException();
			}
			userId = rs.getLong("user_id");
			Long brId = rs.getLong("br_id");
			String loginNo = rs.getString("login_no");
			String userType = rs.getString("user_type");
			String userName = rs.getString("user_name");
			String userSex = rs.getString("user_sex");
			String userStatus = rs.getString("user_status");
			String userPassword = rs.getString("user_password");
			String userPicture = rs.getString("user_picture");
			String userNation = rs.getString("user_nation");
			String userPlace = rs.getString("user_place");
			String userPolity = rs.getString("user_polity");
			String userEmail = rs.getString("user_email");
			String userPhone = rs.getString("user_phone");
			String userRegTime = rs.getString("user_reg_time");
			String modyPassTime = rs.getString("mody_pass_time");
			String lastLoginTime = rs.getString("last_login_time");
			String remark = rs.getString("remark");

			User user = new User();
			user.setUserId(userId);
			user.setBrId(brId);
			user.setLoginNo(loginNo);
			user.setUserType(userType);
			user.setUserName(userName);
			user.setUserSex(userSex);
			user.setUserStatus(userStatus);
			user.setUserPassword(userPassword);
			user.setUserPicture(userPicture);
			user.setUserNation(userNation);
			user.setUserPlace(userPlace);
			user.setUserPolity(userPolity);
			user.setUserEmail(userEmail);
			user.setUserPhone(userPhone);
			user.setUserRegTime(userRegTime);
			user.setModyPassTime(modyPassTime);
			user.setLastLoginTime(lastLoginTime);
			user.setRemark(remark);
			return user;
		} catch (Exception e) {
			throw new UserNotFoundException(e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
	}

	/**
	 * @see UserProvider#loadUserByUsername(String)
	 */
	public User loadUserByUsername(String userName) throws UserNotFoundException {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(LOAD_USER_BY_USERNAME);
			pstmt.setString(1, userName);
			rs = pstmt.executeQuery();
			if (!rs.next()) {
				throw new UserNotFoundException();
			}
			Long userId = rs.getLong("user_id");
			Long brId = rs.getLong("br_id");
			String loginNo = rs.getString("login_no");
			String userType = rs.getString("user_type");
			userName = rs.getString("user_name");
			String userSex = rs.getString("user_sex");
			String userStatus = rs.getString("user_status");
			String userPassword = rs.getString("user_password");
			String userPicture = rs.getString("user_picture");
			String userNation = rs.getString("user_nation");
			String userPlace = rs.getString("user_place");
			String userPolity = rs.getString("user_polity");
			String userEmail = rs.getString("user_email");
			String userPhone = rs.getString("user_phone");
			String userRegTime = rs.getString("user_reg_time");
			String modyPassTime = rs.getString("mody_pass_time");
			String lastLoginTime = rs.getString("last_login_time");
			String remark = rs.getString("remark");

			User user = new User();
			user.setUserId(userId);
			user.setBrId(brId);
			user.setLoginNo(loginNo);
			user.setUserType(userType);
			user.setUserName(userName);
			user.setUserSex(userSex);
			user.setUserStatus(userStatus);
			user.setUserPassword(userPassword);
			user.setUserPicture(userPicture);
			user.setUserNation(userNation);
			user.setUserPlace(userPlace);
			user.setUserPolity(userPolity);
			user.setUserEmail(userEmail);
			user.setUserPhone(userPhone);
			user.setUserRegTime(userRegTime);
			user.setModyPassTime(modyPassTime);
			user.setLastLoginTime(lastLoginTime);
			user.setRemark(remark);

			return user;
		} catch (Exception e) {
			throw new UserNotFoundException(e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
	}

	/**
	 * @see UserProvider#createUser(User)
	 */
	public User createUser(User user) throws Exception {
		try {
			loadUserByUsername(user.getLoginNo());
			// 登录名称已存在,所以:
			throw new UserAlreadyExistsException("User LoginNo  " + user.getLoginNo() + " already exists");
		} catch (UserNotFoundException unfe) {
			String encryptedPassword = null;
			encryptedPassword = DigestUtils.md5Hex(user.getUserPassword());
			Connection con = null;
			PreparedStatement pstmt = null;
			try {
				con = DbConnectionManager.getConnection();
				pstmt = con.prepareStatement(INSERT_USER);
				pstmt.setLong(1, user.getUserId());
				pstmt.setString(2, user.getLoginNo());

				if (null == user.getBrId()) {
					pstmt.setNull(3, Types.BIGINT);
				} else {
					pstmt.setLong(3, user.getBrId());
				}
				if (StringUtils.isEmpty(user.getUserType())) {
					pstmt.setNull(4, Types.VARCHAR);
				} else {
					pstmt.setString(4, user.getUserType());
				}
				if (StringUtils.isEmpty(user.getUserName())) {
					pstmt.setNull(5, Types.VARCHAR);
				} else {
					pstmt.setString(5, user.getUserName());
				}
				if (StringUtils.isEmpty(user.getUserSex())) {
					pstmt.setNull(6, Types.VARCHAR);
				} else {
					pstmt.setString(6, user.getUserSex());
				}
				if (StringUtils.isEmpty(user.getUserStatus())) {
					pstmt.setNull(7, Types.VARCHAR);
				} else {
					pstmt.setString(7, user.getUserStatus());
				}
				if (encryptedPassword == null) {
					pstmt.setNull(8, Types.VARCHAR);
				} else {
					pstmt.setString(8, encryptedPassword);
				}
				if (StringUtils.isEmpty(user.getUserPicture())) {
					pstmt.setNull(9, Types.VARCHAR);
				} else {
					pstmt.setString(9, user.getUserPicture());
				}
				if (StringUtils.isEmpty(user.getUserNation())) {
					pstmt.setNull(10, Types.VARCHAR);
				} else {
					pstmt.setString(10, user.getUserNation());
				}
				if (StringUtils.isEmpty(user.getUserPlace())) {
					pstmt.setNull(11, Types.VARCHAR);
				} else {
					pstmt.setString(11, user.getUserPlace());
				}
				if (StringUtils.isEmpty(user.getUserPolity())) {
					pstmt.setNull(12, Types.VARCHAR);
				} else {
					pstmt.setString(12, user.getUserPolity());
				}
				if (StringUtils.isEmpty(user.getUserEmail())) {
					pstmt.setNull(13, Types.VARCHAR);
				} else {
					pstmt.setString(13, user.getUserEmail());
				}
				if (StringUtils.isEmpty(user.getUserPhone())) {
					pstmt.setNull(14, Types.VARCHAR);
				} else {
					pstmt.setString(14, user.getUserPhone());
				}
				if (StringUtils.isEmpty(user.getUserRegTime())) {
					pstmt.setNull(15, Types.VARCHAR);
				} else {
					pstmt.setString(15, user.getUserRegTime());
				}
				if (StringUtils.isEmpty(user.getModyPassTime())) {
					pstmt.setNull(16, Types.VARCHAR);
				} else {
					pstmt.setString(16, user.getModyPassTime());
				}
				if (StringUtils.isEmpty(user.getLastLoginTime())) {
					pstmt.setNull(17, Types.VARCHAR);
				} else {
					pstmt.setString(17, user.getLastLoginTime());
				}
				if (StringUtils.isEmpty(user.getRemark())) {
					pstmt.setNull(18, Types.VARCHAR);
				} else {
					pstmt.setString(18, user.getRemark());
				}

				pstmt.execute();
			} catch (Exception e) {
				Log.error("创建用户出错！", e);
				throw e;
			} finally {
				DbConnectionManager.closeConnection(pstmt, con);
			}
			return user;
		}
	}

	public void deleteUser(Long userId) {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(DELETE_USER);
			pstmt.setLong(1, userId);
			pstmt.execute();
		} catch (Exception e) {
			Log.error("删除用户出错!", e);
		} finally {
			DbConnectionManager.closeStatement(pstmt);
			DbConnectionManager.closeConnection(pstmt, con);
		}
	}

	public int getUserCount() {
		int count = 0;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(USER_COUNT);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			Log.error("获取用户总数出错！", e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return count;
	}

	public Collection<User> getUsers() {
		Collection<Long> userIds = getUserIds(0, Integer.MAX_VALUE);
		return new UserCollection(userIds.toArray(new Long[userIds.size()]));
	}

	public Collection<Long> getUserIds() {
		return getUserIds(0, Integer.MAX_VALUE);
	}

	private Collection<Long> getUserIds(int startIndex, int numResults) {
		List<Long> userIds = new ArrayList<Long>(500);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			if ((startIndex == 0) && (numResults == Integer.MAX_VALUE)) {
				pstmt = con.prepareStatement(ALL_USERS);
				DbConnectionManager.setFetchSize(pstmt, 500);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					userIds.add(rs.getLong(1));
				}
			} else {
				pstmt = DbConnectionManager.createScrollablePreparedStatement(con, ALL_USERS);
				DbConnectionManager.limitRowsAndFetchSize(pstmt, startIndex, numResults);
				rs = pstmt.executeQuery();
				DbConnectionManager.scrollResultSet(rs, startIndex);
				int count = 0;
				while (rs.next() && count < numResults) {
					userIds.add(rs.getLong(1));
					count++;
				}
			}
			if (Log.isDebugEnabled()) {
				Log.debug("查询结果: " + userIds.size());
				LogResults(userIds);
			}
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return userIds;
	}

	public Collection<User> getUsers(int startIndex, int numResults) {
		Collection<Long> userIds = getUserIds(startIndex, numResults);
		return new UserCollection(userIds.toArray(new Long[userIds.size()]));
	}

	public Collection<User> findUsers(List<String> fields, List<String> querys) {
		return findUsers(fields, querys, 0, Integer.MAX_VALUE);
	}

	public Collection<User> findUsers(List<String> fields, List<String> querys, int startIndex, int numResults) {
		List<Long> userIds = new ArrayList<Long>(50);
		Connection con = null;
		PreparedStatement pstmt = null;
		int queries = 0;
		ResultSet rs = null;
		try {
			StringBuilder sql = new StringBuilder(90);
			sql.append("SELECT user_id FROM app_user WHERE 1=1 ");
			if (fields.contains("br_id")) {
				sql.append(" AND ");
				sql.append(" br_id = ? ");
				queries++;
			}
			if (fields.contains("login_no")) {
				sql.append(" AND ");
				sql.append(" login_no = ? ");
				queries++;
			}
			if (fields.contains("user_name")) {
				sql.append(" AND ");
				sql.append(" user_name LIKE ? ");
				queries++;
			}
			con = DbConnectionManager.getConnection();
			if ((startIndex == 0) && (numResults == Integer.MAX_VALUE)) {
				pstmt = con.prepareStatement(sql.toString());
				for (int i = 0; i < queries; i++) {
					pstmt.setString(i+1, querys.get(i));
				}
				rs = pstmt.executeQuery();
				while (rs.next()) {
					userIds.add(rs.getLong(1));
				}
			} else {
				pstmt = DbConnectionManager.createScrollablePreparedStatement(con, sql.toString());
				DbConnectionManager.limitRowsAndFetchSize(pstmt, startIndex, numResults);
				for (int i = 0; i < queries; i++) {
					pstmt.setString(i+1, querys.get(i));
				}
				rs = pstmt.executeQuery();
				DbConnectionManager.scrollResultSet(rs, startIndex);
				int count = 0;
				while (rs.next() && count < numResults) {
					userIds.add(rs.getLong(1));
					count++;
				}
			}
			if (Log.isDebugEnabled()) {
				Log.debug("查询结果: " + userIds.size());
				LogResults(userIds);
			}
		} catch (SQLException e) {
			Log.error("查询用户信息出错！", e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return new UserCollection(userIds.toArray(new Long[userIds.size()]));
	}

	/**
	 * 记录详细结果。
	 */
	private void LogResults(List<Long> listElements) {
		String callingMethod = Thread.currentThread().getStackTrace()[3].getMethodName();
		StringBuilder sb = new StringBuilder(256);
		int count = 0;
		for (Long element : listElements) {
			if (count > 20) {
				Log.debug(callingMethod + " 结果: " + sb.toString());
				sb.delete(0, sb.length());
				count = 0;
			}
			sb.append(element).append(",");
			count++;
		}
		sb.append("。");
		Log.debug(callingMethod + " 结果: " + sb.toString());
	}

}
